   /*
    EXEC sp_save_assembly_release_lot_bin_info 0, 26896, 1, 'Bin1&Bin2', 702
    EXEC sp_save_assembly_release_lot_bin_info 1, 26896, 8, 'Bin1&Bin2', 702
    SELECT * FROM Assm_Release_Table
    SELECT * FROM Assm_Release_Lot_Table
    SELECT * FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = 26896 AND WAFER_ID = 8
    UPDATE SORT_BIN_RELEASE_TABLE SET BIN_1_BALANCE_QTY = 444 WHERE LOT_ID = 26896 AND WAFER_ID = 8
    SELECT Bin_Number FROM Assembly_Bin_Mapping WHERE ASSM_PROCESSSTEP_ID = 1085 AND BIN_ProcessStep_ID = 1083

	select * from Fab_Lots_Used_For_Assm_Release where lot_id = 
   */

ALTER  PROCEDURE sp_save_assembly_release_lot_bin_info
(
    @assembly_release_id INT,
    @lot_id INT,
    @wafer_id INT,
    @bin_info VARCHAR(50),
    @quantity_pulled INT,
    @bin_info_with_downgrade varchar(100),
    @shipped_lot_number VARCHAR(50)
)
   AS
   BEGIN

	DECLARE @LOT_STATUS VARCHAR(50)
	DECLARE @QTY_IN     INT
	DECLARE @SHIP_QTY   INT
    DECLARE @lot_number VARCHAR(50)
    DECLARE @index INT
    DECLARE @bin_name VARCHAR(50)
    DECLARE @bin_column_name VARCHAR(50)
    DECLARE @bin_balance_column_name VARCHAR(50)
	DECLARE @prv_pulled_qty int

    DECLARE @queryString NVARCHAR(1000)
    DECLARE @ParamDefinition NVARCHAR(500)
    DECLARE @columnsString NVARCHAR(1000)
	DECLARE @shippedLot_No VARCHAR(50)
	DECLARE @NEWLOT_ID  INT
	DECLARE @SHIPPEDLOT_ID INT
    --SELECT @lot_number = LOT_NUMBER, @bin_info = BIN_INFO FROM ASSM_RELEASE_TABLE WHERE Assembly_Release_Id = @assembly_release_id

	SELECT @lot_number = flfa.LOT_NUMBER, @bin_info = flfa.BIN_INFO 
	FROM ASSM_RELEASE_TABLE art, Fab_Lots_Used_For_Assm_Release flfa 
	WHERE art.Assembly_Release_Id = flfa.Assembly_Release_Id and art.Assembly_Release_Id = @assembly_release_id	and flfa.lot_id = @lot_id
	
	SELECT @SHIPPEDLOT_ID = SHIPPEDLot_Id ,  @NEWLOT_ID = SL.Lot_ID FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, PRODUCT P 
			WHERE LT.LOT_ID = SL.LOT_ID  AND SL.STATUS = 'Transit' AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
			AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS.PS_TYPE = 'Die Bank' AND SL.SL_NEXT_PROCESS <> 'Die Bank'
			AND LT.LOT_NUMBER = @shipped_lot_number
	
    IF ( ( SELECT COUNT(*) FROM Assm_Release_Lot_Table WHERE Assembly_Release_Id = @assembly_release_id AND Lot_Id = @lot_id AND Wafer_Id = @wafer_id ) > 0 )
    BEGIN
		SELECT @prv_pulled_qty = Qty_Pulled FROM Assm_Release_Lot_Table WHERE Assembly_Release_Id = @assembly_release_id AND Lot_Id = @lot_id AND Wafer_Id = @wafer_id

        IF ( @quantity_pulled = 0 )
        BEGIN
									
            DELETE FROM Assm_Release_Lot_Table WHERE Assembly_Release_Id = @assembly_release_id AND Lot_Id = @lot_id AND Wafer_Id = @wafer_id
			
            SET @columnsString = ''

            WHILE ( LEN(@bin_info) > 0 )
            BEGIN
                IF ( CHARINDEX ( '&', @bin_info, 1 ) > 0 )
                BEGIN
                    SET @index = CHARINDEX ( '&', @bin_info, 1 )
                    SET @bin_name = SUBSTRING( @bin_info, 0, @index )
                    SET @bin_column_name = REPLACE(@bin_name, 'Bin','Bin_')
                    SET @bin_balance_column_name = REPLACE(@bin_name, 'Bin','Bin_')+'_BALANCE_QTY'
                    SET @columnsString = @columnsString + @bin_balance_column_name + ' = ' + @bin_column_name + ' , '
                    SET @bin_info = SUBSTRING( @bin_info, @index+1, LEN( @bin_info ) )
                END
                ELSE
                BEGIN
                    SET @bin_name = @bin_info
                    SET @bin_column_name = REPLACE(@bin_name, 'Bin','Bin_')
                    SET @bin_balance_column_name = REPLACE(@bin_name, 'Bin','Bin_')+'_BALANCE_QTY'
                    --SET @columnsString = @columnsString + @bin_balance_column_name + ' = ' + @bin_column_name																-- COMMENTED
					SET @columnsString = @columnsString + @bin_balance_column_name + ' = ' + @bin_balance_column_name + '+' + CONVERT( VARCHAR, @prv_pulled_qty )			-- ADDED	
                    SET @bin_info = ''
                END
            END

            SET @queryString = N'UPDATE SORT_BIN_RELEASE_TABLE SET '+@columnsString+' , IS_NOTIFIED = 1 WHERE Lot_Id = @lot_id AND Wafer_Id = @wafer_id'
            SET @ParamDefinition = N'@lot_id int,@wafer_id int'
            EXECUTE sp_executesql @queryString, @ParamDefinition, @lot_id = @lot_id, @wafer_id = @wafer_id

			IF @SHIPPEDLOT_ID > 0
			BEGIN
				SELECT @LOT_STATUS = LOT_CURRENT_STATUS FROM LOT_TABLE WHERE LOT_ID = @lot_id
				IF @LOT_STATUS = 'Close'
				BEGIN
					UPDATE LOT_TABLE SET Lot_current_status = 'Running' WHERE LOT_ID = @lot_id
				END

				UPDATE LOT_TABLE SET QTY_IN = ISNULL(QTY_IN,0) + isNULL(@prv_pulled_qty,0) WHERE LOT_ID = @lot_id
				UPDATE LOT_TABLE SET QTY_IN = ISNULL(QTY_IN,0) - isNULL(@prv_pulled_qty,0) WHERE LOT_ID = @NEWLOT_ID
				UPDATE SHIPPED_LOT SET SHIP_QTY = ISNULL(SHIP_QTY,0) - isNULL(@prv_pulled_qty,0) WHERE SHIPPEDLOT_ID = @SHIPPEDLOT_ID
				SELECT @SHIP_QTY = SHIP_QTY FROM SHIPPED_LOT WHERE SHIPPEDLOT_ID = @SHIPPEDLOT_ID
				IF @SHIP_QTY <= 0
				BEGIN
						DELETE FROM SHIPPED_LOT WHERE SHIPPEDLOT_ID = @SHIPPEDLOT_ID
				END
			END
		END
        ELSE
        BEGIN
			
            UPDATE Assm_Release_Lot_Table SET Qty_Pulled = @quantity_pulled, bin_info = @bin_info_with_downgrade, Shipped_Lot_Number = @shipped_lot_number  WHERE Assembly_Release_Id = @assembly_release_id AND Lot_Id = @lot_id AND Wafer_Id = @wafer_id

            SET @columnsString = ''

            WHILE ( LEN(@bin_info) > 0 )
            BEGIN
                IF ( CHARINDEX ( '&', @bin_info, 1 ) > 0 )
                BEGIN
                    SET @index = CHARINDEX ( '&', @bin_info, 1 )
                    SET @bin_name = SUBSTRING( @bin_info, 0, @index )
                    SET @bin_balance_column_name = REPLACE(@bin_name, 'Bin','Bin_')+'_BALANCE_QTY'
                    SET @columnsString = @columnsString + @bin_balance_column_name + ' =  0, '
                    SET @bin_info = SUBSTRING( @bin_info, @index+1, LEN( @bin_info ) )
                END
                ELSE
                BEGIN
                    SET @bin_name = @bin_info
					SET @bin_column_name = REPLACE(@bin_name, 'Bin','Bin_')																								--ADDED
                    SET @bin_balance_column_name = REPLACE(@bin_name, 'Bin','Bin_')+'_BALANCE_QTY'
                    --SET @columnsString = @columnsString + @bin_balance_column_name + ' =  0 '																			--COMMENTED
					SET @columnsString = @columnsString + @bin_balance_column_name + ' = (' + @bin_balance_column_name + '+' + CONVERT( VARCHAR, @prv_pulled_qty ) + ' - ' + CONVERT( VARCHAR, @quantity_pulled ) + ')'		--ADDED
                    SET @bin_info = ''
                END
            END


            SET @queryString = N'UPDATE SORT_BIN_RELEASE_TABLE SET '+@columnsString+' WHERE Lot_Id = @lot_id AND Wafer_Id = @wafer_id'
            SET @ParamDefinition = N'@lot_id int,@wafer_id int'
            EXECUTE sp_executesql @queryString, @ParamDefinition, @lot_id = @lot_id, @wafer_id = @wafer_id
			
			IF @SHIPPEDLOT_ID > 0
			BEGIN
				SELECT @LOT_STATUS = LOT_CURRENT_STATUS FROM LOT_TABLE WHERE LOT_ID = @lot_id
				IF @LOT_STATUS = 'Close'
				BEGIN
					UPDATE LOT_TABLE SET Lot_current_status = 'Running' WHERE LOT_ID = @lot_id
				END

				UPDATE LOT_TABLE SET QTY_IN = ISNULL(QTY_IN,0) + ( isNULL(@prv_pulled_qty,0) - isNull(@quantity_pulled, 0 ) ) WHERE LOT_ID = @lot_id
				UPDATE LOT_TABLE SET QTY_IN = ISNULL(QTY_IN,0) - ( isNULL(@prv_pulled_qty,0) - isNull(@quantity_pulled, 0 ) ) WHERE LOT_ID = @NEWLOT_ID
				UPDATE SHIPPED_LOT SET SHIP_QTY = ISNULL(SHIP_QTY,0) - ( isNULL(@prv_pulled_qty,0) - isNull(@quantity_pulled, 0 ) ) WHERE SHIPPEDLOT_ID = @SHIPPEDLOT_ID 	
				SELECT @SHIP_QTY = SHIP_QTY FROM SHIPPED_LOT WHERE SHIPPEDLOT_ID = @SHIPPEDLOT_ID
				IF @SHIP_QTY <= 0
				BEGIN
						DELETE FROM SHIPPED_LOT WHERE SHIPPEDLOT_ID = @SHIPPEDLOT_ID
				END
			END
        END
    END
    ELSE
    BEGIN

        IF ( @quantity_pulled > 0 )
        BEGIN
			
            INSERT INTO Assm_Release_Lot_Table ( Assembly_Release_Id, Lot_Id, Wafer_Id, Qty_Pulled, bin_info, Shipped_Lot_Number ) VALUES
                               ( @assembly_release_id, @lot_id,  @wafer_id, @quantity_pulled, @bin_info_with_downgrade, @shipped_lot_number )

            SET @columnsString = ''

            WHILE ( LEN(@bin_info) > 0 )
            BEGIN
                IF ( CHARINDEX ( '&', @bin_info, 1 ) > 0 )
                BEGIN
                    SET @index = CHARINDEX ( '&', @bin_info, 1 )
                    SET @bin_name = SUBSTRING( @bin_info, 0, @index )
                    SET @bin_balance_column_name = REPLACE(@bin_name, 'Bin','Bin_')+'_BALANCE_QTY'
                    SET @columnsString = @columnsString + @bin_balance_column_name + ' =  0, '
                    SET @bin_info = SUBSTRING( @bin_info, @index+1, LEN( @bin_info ) )
                END
                ELSE
                BEGIN
                    SET @bin_name = @bin_info
					SET @bin_column_name = REPLACE(@bin_name, 'Bin','Bin_')																							--ADDED
                    SET @bin_balance_column_name = REPLACE(@bin_name, 'Bin','Bin_')+'_BALANCE_QTY'
                    --SET @columnsString = @columnsString + @bin_balance_column_name + ' =  0 '																		--COMMENTED
					SET @columnsString = @columnsString + @bin_balance_column_name + ' = (' + @bin_balance_column_name + ' - ' + CONVERT( VARCHAR, @quantity_pulled ) + ')'	--ADDED
                    SET @bin_info = ''
                END
            END


            SET @queryString = N'UPDATE SORT_BIN_RELEASE_TABLE SET '+ @columnsString + ' WHERE Lot_Id = @lot_id AND Wafer_Id = @wafer_id'
            SET @ParamDefinition = N'@lot_id int,@wafer_id int'
            EXECUTE sp_executesql @queryString, @ParamDefinition, @lot_id = @lot_id, @wafer_id = @wafer_id
			
			IF @SHIPPEDLOT_ID > 0
			BEGIN
				
				UPDATE LOT_TABLE SET QTY_IN = ISNULL(QTY_IN,0) - isNULL(@quantity_pulled,0) WHERE LOT_ID = @lot_id
				
				UPDATE LOT_TABLE SET QTY_IN = ISNULL(QTY_IN,0) + isNull(@quantity_pulled, 0 ) WHERE LOT_ID = @NEWLOT_ID
				UPDATE SHIPPED_LOT SET SHIP_QTY = ISNULL(SHIP_QTY,0) + isNull(@quantity_pulled, 0 ) WHERE SHIPPEDLOT_ID = @SHIPPEDLOT_ID
				UPDATE Assm_Release_Lot_Table SET Lot_Transferred = 0 WHERE LOT_ID = @lot_id AND Wafer_ID = @wafer_id AND ASSEMBLY_RELEASE_ID = @assembly_release_id
				SELECT @QTY_IN = ISNULL(QTY_IN, 0) FROM LOT_TABLE WHERE LOT_ID = @lot_id
				IF @QTY_IN =  0
				BEGIN
					UPDATE LOT_TABLE SET Lot_current_status = 'Close' WHERE LOT_ID = @lot_id
				END					
			END
        END
    END

END
